﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cmsservice.Entity;

namespace cmsservice.DAL
{
    public class F10GdyjDAL
    {

        /// <summary>
        /// 占流通股比例
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<ZltgblEntity> GetZltgbl(string code)
        {
            List<ZltgblEntity> lst = new List<ZltgblEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" select top 100 SecuCode, SecuAbbr, CirculatingRatio,EndDate from EM_F10_ProportionInstitutionalOwnership, SECURITY");
            sb.AppendFormat(" where OrgID=SecuID AND SecuCode='{0}'", code);
            sb.AppendFormat(" order by EndDate desc, SECURITY.SecuCode");
            lst = SqlHelper.GetDataByColumnsLst<ZltgblEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 潜在分红能力分析
        /// </summary>
        /// <returns></returns>
        public List<FhrzEntity> GetFhrz()
        {
            List<FhrzEntity> lst = new List<FhrzEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.* ");
            sb.AppendFormat(" ,NewTotalShareAmountNoMax=(SELECT MAX(NewTotalShareAmountNo) FROM EM_F10_ShareOutAbilityAnalysis)");
            sb.AppendFormat(" ,ShareOutThanFinancingNoMax=(SELECT MAX(ShareOutThanFinancingNo) FROM EM_F10_ShareOutAbilityAnalysis)");
            sb.AppendFormat(" FROM EM_F10_ShareOutAbilityAnalysis sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID");
            lst = SqlHelper.GetDataByColumnsLst<FhrzEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 财务分析-盈利能力
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>

        public List<YlnlLstEntity> GetCwYlnl(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<YlnlLstEntity> lst = new List<YlnlLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.ReportPeriod,NetAssetsIncomeRate");
            sb.AppendFormat(" ,NetAssetsIncomeRateAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(NetAssetsIncomeRate) FROM EM_F10_Profitability b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN("+stockStr+")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_Profitability sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            //sb.AppendFormat("select NetAssetsIncomeRate,ProfitabilityIndex as NetAssetsIncomeRateAvg,* from EM_F10_Profitability where OrgID='{0}'  order by ReportPeriod desc", code);
            lst = SqlHelper.GetDataByColumnsLst<YlnlLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }
        public List<HYGL> GetGgInBk()
        {
            List<HYGL> lst = new List<HYGL>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("select a.SecuCode,a.SecuAbbr,c.BlockCode,c.BlockName,c.BlockCategory from SECURITY as a inner join EM_BLOCK_STOCK as b on a.SecuCode=b.SecuCode inner join EM_BLOCK_INFO as c on b.BlockCode=c.BlockCode");
           
            //sb.AppendFormat("select NetAssetsIncomeRate,ProfitabilityIndex as NetAssetsIncomeRateAvg,* from EM_F10_Profitability where OrgID='{0}'  order by ReportPeriod desc", code);
            lst = SqlHelper.GetDataByColumnsLst<HYGL>(DBConn.NewDMSConnstr, sb.ToString(), null);
            return lst;
        }
        public List<F10_IndustryNews> GetHYNewsLst(string bkId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select top 100 * from (");
            sb.AppendLine("select Id,BKID,Title,PublDate as PTime  from F10_IndustryNews");
            sb.AppendLine("inner join");
            sb.AppendLine("(");
            sb.AppendLine("select (MAX(Id)) as AA from F10_IndustryNews where BKID=@BKID");
            sb.AppendLine("group by Title");
            sb.AppendLine(") as a");
            sb.AppendLine("on F10_IndustryNews.Id =a.AA) dd order by dd.PublDate desc");
            Tools tool = new Tools();
            tool.AddSqlParameter("BKID", System.Data.SqlDbType.VarChar, 11, bkId);
            return SqlHelper.GetDataByColumnsLst<F10_IndustryNews>(DBConn.TempF10, sb.ToString(), tool.GetSqlParameters());
        }
        public List<F10_NewsComments> GetLst(string stock)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select top 100 Id,Title,PublDate as PTime  from F10_NewsComments");
            sb.AppendLine("inner join (");
            sb.AppendLine("select MAX(Id) as cc from F10_NewsComments where Stock=@Stock group by Title) as bb");
            sb.AppendLine("on F10_NewsComments.Id=bb.cc");
            sb.AppendLine("order by PublDate desc");
            Tools tool = new Tools();
            tool.AddSqlParameter("Stock", System.Data.SqlDbType.VarChar, 11, stock);
            return SqlHelper.GetDataByColumnsLst<F10_NewsComments>(DBConn.TempF10, sb.ToString(), tool.GetSqlParameters());
        }
       

        public Em_Block_Stock GetOne(string stock)
        {
            string sql = "select b.*,e.BlockName from Em_Block_Stock b INNER JOIN EM_BLOCK_INFO e ON b.BlockCode=e.BlockCode where b.SecuCode=@stock and b.BlockCode like 'BK2%' and b.IsValid='1'";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", System.Data.SqlDbType.VarChar, 11, stock);
            return SqlHelper.GetDataByColumns<Em_Block_Stock>(DBConn.NewDMSConnstr, sql, tool.GetSqlParameters());
        }
       

        /// <summary>
        /// 财务分析-收益质量
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<SyzlLstEntity> GetCwSyzl(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr= em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<SyzlLstEntity> lst = new List<SyzlLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.ReportPeriod,NetIncomeManagementActivities");
            sb.AppendFormat(" ,ActivitiesAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(NetIncomeManagementActivities) FROM EM_F10_EarningQuality b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.Append("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN("+stockStr+")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_EarningQuality sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<SyzlLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }


        /// <summary>
        /// 财务分析-现金流量
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<XjllLstEntity> GetCwXjll(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<XjllLstEntity> lst = new List<XjllLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.ReportPeriod,OperatingActivitiesAmountCashFlow");
            sb.AppendFormat(" ,CashFlowAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(OperatingActivitiesAmountCashFlow) FROM EM_F10_CashFlow b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN("+stockStr+")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_CashFlow sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<XjllLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 财务分析-资本结构
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<ZbjgLstEntity> GetCwZbjg(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<ZbjgLstEntity> lst = new List<ZbjgLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.ReportPeriod,AssetliabilityRatio");
            sb.AppendFormat(" ,RatioAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(AssetliabilityRatio) FROM EM_F10_CapitalStructure b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN(" + stockStr + ")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_CapitalStructure sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<ZbjgLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 财务分析-偿债能力
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<CznlLstEntity> GetCwCznl(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<CznlLstEntity> lst = new List<CznlLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr, sh.ReportPeriod,LiquidityRatio");
            sb.AppendFormat(" ,LiquidityRatioAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(LiquidityRatio) FROM EM_F10_DebtPayingAbility b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN(" + stockStr + ")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_DebtPayingAbility sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<CznlLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 财务分析-营运能力
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<YynlLstEntity> GetCwYynl(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<YynlLstEntity> lst = new List<YynlLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr,sh.ReportPeriod,OperatingCycle");
            sb.AppendFormat(" ,OperatingCycleAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(OperatingCycle) FROM EM_F10_OperatingCapacity b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN(" + stockStr + ")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_OperatingCapacity sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<YynlLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

        /// <summary>
        /// 财务分析-成长能力
        /// </summary>
        /// <param name="code"></param>
        /// <param name="bkcode"></param>
        /// <returns></returns>
        public List<CzhnlLstEntity> GetCwCzhnl(string code, string bkcode)
        {
            Em_Block_StockDal em_Block_StockDAL = new Em_Block_StockDal();
            string stockStr = em_Block_StockDAL.GetStockByBlock_Str(bkcode);
            List<CzhnlLstEntity> lst = new List<CzhnlLstEntity>();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" SELECT se.SecuCode,se.SecuAbbr,sh.ReportPeriod,OperatingIncomeGrowthRate");
            sb.AppendFormat(" ,GrowthRateAvg=");
            sb.AppendFormat(" (");
            sb.AppendFormat(" SELECT AVG(OperatingIncomeGrowthRate) FROM EM_F10_GrowthAbility b");
            sb.AppendFormat(" WHERE b.ReportPeriod=sh.ReportPeriod");
            sb.AppendFormat("  AND  OrgID IN(");
            sb.AppendFormat("  SELECT SecuID FROM dbo.SECURITY WHERE SecuCode IN(" + stockStr + ")");
            sb.AppendFormat("  AND SecuCategory='001001')");
            sb.AppendFormat(" )");
            sb.AppendFormat(" FROM EM_F10_GrowthAbility sh,SECURITY se");
            sb.AppendFormat(" WHERE OrgID=SecuID AND secucode='{0}'", code);
            sb.AppendFormat(" ORDER BY ReportPeriod DESC");
            lst = SqlHelper.GetDataByColumnsLst<CzhnlLstEntity>(DBConn.F10, sb.ToString(), null);
            return lst;
        }

    }
    [Serializable]
    public class Em_Block_Stock
    {
        public string SecuCode
        {
            get;
            set;
        }

        public string BlockCode
        {
            get;
            set;
        }
        public string BlockName
        {
            get;
            set;
        }
    }
}
